package com.huatai.bpmn.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.huatai.bpmn.entity.TbCompleteTaskStatusEntity;
import com.huatai.bpmn.entity.YYDataInfoEntity;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author yxy
*/
public interface YYDataInfoMapper extends BaseMapper<YYDataInfoEntity> {

	@Select("select flow_id from yy_data_info where my_key=\"${myKey}\" order by create_time desc limit ${pgIndex},${pgSize}")
	List<String> getFlowList(String myKey,int pgIndex,int pgSize);



//	@Select("select flow_id from yy_data_info where my_key=\"${myKey}\" and creator in #{str} limit ${pgIndex},${pgSize}")
//	List<String> getFlowListStr(String myKey,String str, String pgIndex, String pgSize);

	/**
	 * 查询现金流小于0
	 * @return
	 */
	@Select("select business_key from pmp_basic_load  WHERE json_key=\"xjl\" and json_val < 0 limit ${pgIndex},${pgSize}")
	List<String> getYJFlowList(int pgIndex,int pgSize);

	/**
	 * 查询现金流小于0
	 * @return
	 */
	@Select("select count(business_key) from pmp_basic_load  WHERE json_key=\"xjl\" and json_val < 0")
	int getYJFlowListCount();

	@Select("select count(flow_id) from yy_data_info where my_key=\"${myKey}\"")
	int getFlowListCount(String myKey);





	@Select("SELECT flow_id from yy_data_info where YEAR(create_time)=${year} and my_key=\"${myKey}\"")
	List<String> listByYear(int year,String myKey);

	@Select("SELECT business_key from pmp_basic_load where json_key=\"gkbm\" and json_val=\"${deptName}\" and business_key in ${str};")
	List<String> yearListByDepart(String deptName,String str);

	@Select("SELECT flow_id from yy_data_info where YEAR(create_time)=${year} and my_key=\"tongdao\"")
	List<String> tdYearList(int year);


	@Select("select flow_id from yy_data_info where source=\"${myKey}\" order by create_time desc limit ${pgIndex},${pgSize}")
	List<String> getFlowListByMyKey(String myKey, int pgIndex, int pgSize);
	@Select("select count(flow_id) from yy_data_info where source=\"${myKey}\"")
	int getFlowListCountByMyKey(String myKey);


//	@Select("select a.flow_id from yy_data_info a left join pmp_basic_load b on a.flow_id=b.business_key where a.source=\"${myKey}\" and b.json_key=\"gkbm\" and b.json_val=\"${departName}\" limit ${pgIndex},${pgSize}")
//	@Select("select a.flow_id from yy_data_info a left join pmp_basic_load b on a.flow_id=b.business_key where a.source=\"${myKey}\" ${str} limit ${pgIndex},${pgSize}")
	@Select("select a.flow_id from yy_data_info a left join(\n" +
		"SELECT\n" +
		"a.business_key,\n" +
		"MAX(IF(a.json_key = 'gkbm', json_val, NULL)) AS 'gkbm',\n" +
		"MAX(IF(a.json_key = 'xmmc', json_val, NULL)) AS 'xmmc',\n" +
		"MAX(IF(a.json_key = 'xmdj', json_val, NULL)) AS 'xmdj',\n" +
		"MAX(IF(a.json_key = 'yjqdsj', json_val, NULL)) AS 'yjqdsj'\n" +
		"from pmp_basic_load a GROUP BY a.business_key) b on a.flow_id=b.business_key where a.source=\"${myKey}\" ${str} order by a.create_time desc limit ${pgIndex},${pgSize}")
	List<String> getFlowListByDepartNameByMyKey(String myKey, String str, int pgIndex, int pgSize);
//	@Select("select count(a.flow_id) from yy_data_info a left join pmp_basic_load b on a.flow_id=b.business_key where a.source=\"${myKey}\" and b.json_key=\"gkbm\" and b.json_val=\"${departName}\"")
@Select("select count(a.flow_id) from yy_data_info a left join(\n" +
	"SELECT\n" +
	"a.business_key,\n" +
	"MAX(IF(a.json_key = 'gkbm', json_val, NULL)) AS 'gkbm',\n" +
	"MAX(IF(a.json_key = 'xmmc', json_val, NULL)) AS 'xmmc',\n" +
	"MAX(IF(a.json_key = 'xmdj', json_val, NULL)) AS 'xmdj',\n" +
	"MAX(IF(a.json_key = 'yjqdsj', json_val, NULL)) AS 'yjqdsj'\n" +
	"from pmp_basic_load a GROUP BY a.business_key) b on a.flow_id=b.business_key where a.source=\"${myKey}\" ${str}")
	int getFlowListByDepartNameCountByMyKey(String myKey, String str);

//	@Select("select a.flow_id from yy_data_info a left join pmp_basic_load b on a.flow_id=b.business_key where a.my_key=\"${myKey}\" and b.json_key=\"gkbm\" and b.json_val=\"${departName}\" limit ${pgIndex},${pgSize}")
//	@Select("select a.flow_id from yy_data_info a left join pmp_basic_load b on a.flow_id=b.business_key where a.my_key=\"${myKey}\" ${str} limit ${pgIndex},${pgSize}")
@Select("select a.flow_id from yy_data_info a left join(\n" +
	"SELECT\n" +
	"a.business_key,\n" +
	"MAX(IF(a.json_key = 'gkbm', json_val, NULL)) AS 'gkbm',\n" +
	"MAX(IF(a.json_key = 'xmmc', json_val, NULL)) AS 'xmmc',\n" +
	"MAX(IF(a.json_key = 'xmdj', json_val, NULL)) AS 'xmdj',\n" +
	"MAX(IF(a.json_key = 'yjqdsj', json_val, NULL)) AS 'yjqdsj'\n" +
	"from pmp_basic_load a GROUP BY a.business_key) b on a.flow_id=b.business_key where a.my_key=\"${myKey}\" ${str} order by a.create_time desc limit ${pgIndex},${pgSize}")
	List<String> getFlowListByDepartName(String myKey,String str,int pgIndex,int pgSize);

//	@Select("select count(a.flow_id) from yy_data_info a left join pmp_basic_load b on a.flow_id=b.business_key where a.my_key=\"${myKey}\" and b.json_key=\"gkbm\" and b.json_val=\"${departName}\"")
//	@Select("select count(a.flow_id) from yy_data_info a left join pmp_basic_load b on a.flow_id=b.business_key where a.my_key=\"${myKey}\" ${str}")
@Select("select count(a.flow_id) from yy_data_info a left join(\n" +
	"SELECT\n" +
	"a.business_key,\n" +
	"MAX(IF(a.json_key = 'gkbm', json_val, NULL)) AS 'gkbm',\n" +
	"MAX(IF(a.json_key = 'xmmc', json_val, NULL)) AS 'xmmc',\n" +
	"MAX(IF(a.json_key = 'xmdj', json_val, NULL)) AS 'xmdj',\n" +
	"MAX(IF(a.json_key = 'yjqdsj', json_val, NULL)) AS 'yjqdsj'\n" +
	"from pmp_basic_load a GROUP BY a.business_key) b on a.flow_id=b.business_key where a.my_key=\"${myKey}\" ${str}")
	int getFlowListByDepartNameCount(String myKey, String str);

	@Select("select flow_id from yy_data_info where YEAR(create_time)=${year} and flow_id in\n" +
		"(SELECT business_key from pmp_basic_load where json_key=\"gkbm\" and json_val=\"${deptName}\");")
	List<String> ListByDepart(String deptName,int year);

	@Select("SELECT flow_id from yy_data_info where my_key=\"${myKey}\"")
	List<String> myKeyList(String myKey);

}




